using System.Linq;
using System.Text;
using System.Diagnostics;
using System.Runtime.Serialization;
using System.Collections;
using System.Data;
using System.ComponentModel;
using System.Data.OracleClient;
using WPFExcelReport.Helpers;
using WPFExcelReport.Controls;
namespace WPFExcelReport
{
    public partial class VWBHIST
    {
        public DataRow Convert2WBHIST(DataRow dwInput, string ITXDBVERSION, bool IsConvert = false)
        {
            DataSet SResult = new DataSet();
            SResult = QueryWBHIST(dwInput, 0);
            DataRow dwReturn = null;
            if (SResult.Tables[0].Rows.Count > 0)
            {
                dwReturn = SResult.Tables[0].Rows[0];
            }
            else
            {
                dwReturn = SResult.Tables[0].NewRow();
            }
            switch (ITXDBVERSION)
            {
                case "YGR":
                    #region=========
         dwReturn["WBHMANDANT"] = " ".PadRight(2 ); 
         dwReturn["WBHDATE"] = " ".PadRight(8 ); 
         dwReturn["WBHRWKRS"] = " ".PadRight(2 ); 
         dwReturn["WBHRWLFD"] = " ".PadRight(6 ); 
         dwReturn["WBHPOS"] = " ".PadRight(4 ); 
         dwReturn["WBHWERK"] = " ".PadRight(3 ); 
         dwReturn["WBHABT"] = " ".PadRight(3 ); 
         dwReturn["WBHKUNDNR"] = " ".PadRight(8 ); 
         dwReturn["WBHSTKNR"] = " ".PadRight(12 ); 
         dwReturn["WBHWAKZ"] = " ".PadRight(1 ); 
         dwReturn["WBHART"] = " ".PadRight(20 ); 
         dwReturn["WBHDESS"] = " ".PadRight(10 ); 
         dwReturn["WBHCOLORIT"] = " ".PadRight(10 ); 
         dwReturn["WBHFARBE"] = " ".PadRight(10 ); 
         dwReturn["WBHBESCHICH"] = " ".PadRight(10 ); 
         dwReturn["WBHZUSTAND"] = " ".PadRight(4 ); 
         dwReturn["WBHVAR"] = " ".PadRight(2 ); 
         dwReturn["WBHKOLL"] = " ".PadRight(5 ); 
         dwReturn["WBHLO"] = " ".PadRight(2 ); 
         dwReturn["WBHLB"] = " ".PadRight(2 ); 
         dwReturn["WBHLP"] = " ".PadRight(8 ); 
         dwReturn["WBHFERTKZ"] = " ".PadRight(1 ); 
         dwReturn["WBHZS"] = " ".PadRight(2 ); 
         dwReturn["WBHTAX"] = " ".PadRight(3 ); 
         dwReturn["WBHISTKLG"] = " ".PadRight(0 ); 
         dwReturn["WBHNETKLG"] = " ".PadRight(0 ); 
         dwReturn["WBHIGEW"] = " ".PadRight(0 ); 
         dwReturn["WBHIBRT"] = " ".PadRight(0 ); 
         dwReturn["WBHAUFMSL"] = " ".PadRight(2 ); 
         dwReturn["WBHZUSAM"] = " ".PadRight(2 ); 
         dwReturn["WBHPACKTYP"] = " ".PadRight(4 ); 
         dwReturn["WBHSPSTA"] = " ".PadRight(2 ); 
         dwReturn["WBHSPGRD"] = " ".PadRight(2 ); 
         dwReturn["WBHSPKZ"] = " ".PadRight(1 ); 
         dwReturn["WBHANZTEILE"] = " ".PadRight(1 ); 
         dwReturn["WBHRETOURKZ"] = " ".PadRight(2 ); 
         dwReturn["WBHHHK1"] = " ".PadRight(0 ); 
         dwReturn["WBHHHK2"] = " ".PadRight(0 ); 
         dwReturn["WBHHHK3"] = " ".PadRight(0 ); 
         dwReturn["WBHHHK4"] = " ".PadRight(0 ); 
         dwReturn["WBHHHK5"] = " ".PadRight(0 ); 
         dwReturn["WBHHHK6"] = " ".PadRight(0 ); 
         dwReturn["WBHHHK7"] = " ".PadRight(0 ); 
         dwReturn["WBHHHK8"] = " ".PadRight(0 ); 
         dwReturn["WBHHHK9"] = " ".PadRight(0 ); 
         dwReturn["WBHHHK10"] = " ".PadRight(0 ); 
         dwReturn["WBHHHKG"] = " ".PadRight(0 ); 
         dwReturn["WBHAHK1"] = " ".PadRight(0 ); 
         dwReturn["WBHAHK2"] = " ".PadRight(0 ); 
         dwReturn["WBHAHK3"] = " ".PadRight(0 ); 
         dwReturn["WBHAHK4"] = " ".PadRight(0 ); 
         dwReturn["WBHAHK5"] = " ".PadRight(0 ); 
         dwReturn["WBHAHK6"] = " ".PadRight(0 ); 
         dwReturn["WBHAHK7"] = " ".PadRight(0 ); 
         dwReturn["WBHAHK8"] = " ".PadRight(0 ); 
         dwReturn["WBHAHK9"] = " ".PadRight(0 ); 
         dwReturn["WBHAHK10"] = " ".PadRight(0 ); 
         dwReturn["WBHAHKG"] = " ".PadRight(0 ); 
         dwReturn["WBHABPZAB"] = " ".PadRight(0 ); 
         dwReturn["WBHBPREIS"] = " ".PadRight(0 ); 
         dwReturn["WBHPART"] = " ".PadRight(5 ); 
         dwReturn["WBHAVPREIS"] = " ".PadRight(0 ); 
         dwReturn["WBHIPREIS"] = " ".PadRight(0 ); 
         dwReturn["WBHINDAT"] = " ".PadRight(0 ); 
         dwReturn["WBHFILLER"] = " ".PadRight(19 ); 
                    #endregion
                    break;
                case "JLT":

                    break;
                default:

                    break;
            }
            if (IsConvert)
            {
                foreach (DataColumn cm in dwInput.Table.Columns)
                {
                    if (dwInput[cm.ColumnName].ToString() == "")
                        continue;
                    if (dwReturn.Table.Columns.Contains(cm.ColumnName))
                    {
                        dwReturn[cm.ColumnName] = dwInput[cm.ColumnName];
                    }
                }
            }
            return dwReturn;
        }
        public void MapWBHIST(ref DataRow dwLocal, DataRow dwData)
        {
            BlindMap(ref dwLocal, dwData);
        }
        public void SaveWBHIST(DataRow dwData, string ITXDBVERSION = "50.24")
        {
            DataSet setResult = new DataSet();
            DataTable tbLocal = null;
            DataRow dwLocal = null;
            bool flgExist = false;
            setResult = QueryWBHIST(dwData);
            if (setResult.Tables.Count > 0)
            {
                tbLocal = setResult.Tables[0];
                if (tbLocal.Rows.Count > 0)
                {
                    flgExist = true;
                    dwLocal = tbLocal.Rows[0];
                }
                else
                {
                    dwLocal = tbLocal.NewRow();
                }
            }
            MapWBHIST(ref dwLocal, dwData);

            if (!flgExist)
            {

                InsertWBHIST(dwLocal, ITXDBVERSION);
            }
            UpdateWBHIST(dwLocal);
        }
        public void InsertWBHIST(DataRow dwData, string ITXDBVERSION = "50.24")
        {
            OracleCommand or1;
            or1 = OracleDB.CreateCommand();
            switch (ITXDBVERSION)
            {
                case "YGR":
                    or1.CommandText = "INSERT INTO WBHIST(VAKBMANDANT, VAKBKOLL, VAKBART, VAKBKUNDNR, VAKBFARBE, VAKBKDARTNR, VAKBKDARTBEZ, VAKBKDFARBNR, VAKBANDRUCK, VAKBMEGEW, VAKBMENGEH, VAKBGEWVON, VAKBGEWBIS, VAKBGEWCA, VAKBGEWFKT, VAKBGEWGRP, VAKBMEGFKT, VAKBMEGGRP, VAKBAENDZL, VAKBAENDBEN, VAKBAENDDAT, VAKBNEUBEN, VAKBNEUDAT, VAKBFILLER)"
                        + " VALUES (:VAKBMANDANT, :VAKBKOLL, :VAKBART, :VAKBKUNDNR, :VAKBFARBE, '                         ', '                         ', '                             ', ' ', '   ', '   ', 0, 0, ' ', '000000000000', ' ', '000000000000', ' ', 0, '        ', '000000', '        ', '      ', '                                          ')";
                    or1.Parameters.Add("VAKBMANDANT", OracleType.NVarChar).Value = dwData["VAKBMANDANT"].ToString();
                    or1.Parameters.Add("VAKBKOLL", OracleType.NVarChar).Value = dwData["VAKBKOLL"].ToString();
                    or1.Parameters.Add("VAKBART", OracleType.NVarChar).Value = dwData["VAKBART"].ToString();
                    or1.Parameters.Add("VAKBFARBE", OracleType.NVarChar).Value = dwData["VAKBFARBE"].ToString();
                    or1.Parameters.Add("VAKBKUNDNR", OracleType.NVarChar).Value = dwData["VAKBKUNDNR"].ToString();
                    break;
                //VAFMANDANT VAFKOLL VAFART VAFFARBE VAFBEZ VAFFFARBE

            }
            
            Connect2Proxy(or1);
        }
        public DataSet QueryWBHIST(DataRow dwData, int option = 1)
        {
            DataSet Return;
            DataSet Result = new DataSet();
            OracleCommand or1 = OracleDB.CreateCommand();
            switch (option)
            {
                case 0:
                    or1.CommandText = "SELECT * FROM WBHIST WHERE  ROWNUM<2 ";
                    break;
              
                case 99:
                    or1.CommandText = "SELECT * FROM WBHIST  WHERE ROWNUM<50";
                    break;
                default:
                    break;
            }
            Result = Connect2Proxy(or1);
            if (Result.Tables.Count > 0)
                Result.Tables[0].TableName = "WBHIST";
            Return = Result;
            return Return;
        }
        public void DeleteWBHIST(DataRow dwData, int option = 1)
        {

            OracleCommand or1 = OracleDB.CreateCommand();
            switch (option)
            {
                case 1:
                    or1.CommandText = "DELETE FROM WBHIST   WHERE  VAKBMANDANT=:VAKBMANDANT AND VAKBKOLL=:VAKBKOLL AND VAKBART =:VAKBART AND VAKBFARBE=:VAKBFARBE AND VAKBKUNDNR=:VAKBKUNDNR";
                     or1.Parameters.Add("VAKBMANDANT", OracleType.NVarChar).Value = dwData["VAKBMANDANT"].ToString();
                    or1.Parameters.Add("VAKBKOLL", OracleType.NVarChar).Value = dwData["VAKBKOLL"].ToString();
                    or1.Parameters.Add("VAKBART", OracleType.NVarChar).Value = dwData["VAKBART"].ToString();
                    or1.Parameters.Add("VAKBFARBE", OracleType.NVarChar).Value = dwData["VAKBFARBE"].ToString();
                    or1.Parameters.Add("VAKBKUNDNR", OracleType.NVarChar).Value = dwData["VAKBKUNDNR"].ToString();
                    break;
                default:
                    break;
            }
            Connect2Proxy(or1);
        } 
      
        public void UpdateWBHIST(DataRow dwData, string ITXDBVERSION = "50.24", int option = 1)
        {
            OracleCommand or1;
            DataSet Result = new DataSet();
            or1 = OracleDB.CreateCommand();
            switch (option)
            {
                case 1:
                    #region====Command==== 
                    or1.CommandText = "UPDATE WBHIST SET "
                    +"WBHMANDANT= :WBHMANDANT,"
                    +"WBHDATE= :WBHDATE,"
                    +"WBHRWKRS= :WBHRWKRS,"
                    +"WBHRWLFD= :WBHRWLFD,"
                    +"WBHPOS= :WBHPOS,"
                    +"WBHWERK= :WBHWERK,"
                    +"WBHABT= :WBHABT,"
                    +"WBHKUNDNR= :WBHKUNDNR,"
                    +"WBHSTKNR= :WBHSTKNR,"
                    +"WBHWAKZ= :WBHWAKZ,"
                    +"WBHART= :WBHART,"
                    +"WBHDESS= :WBHDESS,"
                    +"WBHCOLORIT= :WBHCOLORIT,"
                    +"WBHFARBE= :WBHFARBE,"
                    +"WBHBESCHICH= :WBHBESCHICH,"
                    +"WBHZUSTAND= :WBHZUSTAND,"
                    +"WBHVAR= :WBHVAR,"
                    +"WBHKOLL= :WBHKOLL,"
                    +"WBHLO= :WBHLO,"
                    +"WBHLB= :WBHLB,"
                    +"WBHLP= :WBHLP,"
                    +"WBHFERTKZ= :WBHFERTKZ,"
                    +"WBHZS= :WBHZS,"
                    +"WBHTAX= :WBHTAX,"
                    +"WBHISTKLG= :WBHISTKLG,"
                    +"WBHNETKLG= :WBHNETKLG,"
                    +"WBHIGEW= :WBHIGEW,"
                    +"WBHIBRT= :WBHIBRT,"
                    +"WBHAUFMSL= :WBHAUFMSL,"
                    +"WBHZUSAM= :WBHZUSAM,"
                    +"WBHPACKTYP= :WBHPACKTYP,"
                    +"WBHSPSTA= :WBHSPSTA,"
                    +"WBHSPGRD= :WBHSPGRD,"
                    +"WBHSPKZ= :WBHSPKZ,"
                    +"WBHANZTEILE= :WBHANZTEILE,"
                    +"WBHRETOURKZ= :WBHRETOURKZ,"
                    +"WBHHHK1= :WBHHHK1,"
                    +"WBHHHK2= :WBHHHK2,"
                    +"WBHHHK3= :WBHHHK3,"
                    +"WBHHHK4= :WBHHHK4,"
                    +"WBHHHK5= :WBHHHK5,"
                    +"WBHHHK6= :WBHHHK6,"
                    +"WBHHHK7= :WBHHHK7,"
                    +"WBHHHK8= :WBHHHK8,"
                    +"WBHHHK9= :WBHHHK9,"
                    +"WBHHHK10= :WBHHHK10,"
                    +"WBHHHKG= :WBHHHKG,"
                    +"WBHAHK1= :WBHAHK1,"
                    +"WBHAHK2= :WBHAHK2,"
                    +"WBHAHK3= :WBHAHK3,"
                    +"WBHAHK4= :WBHAHK4,"
                    +"WBHAHK5= :WBHAHK5,"
                    +"WBHAHK6= :WBHAHK6,"
                    +"WBHAHK7= :WBHAHK7,"
                    +"WBHAHK8= :WBHAHK8,"
                    +"WBHAHK9= :WBHAHK9,"
                    +"WBHAHK10= :WBHAHK10,"
                    +"WBHAHKG= :WBHAHKG,"
                    +"WBHABPZAB= :WBHABPZAB,"
                    +"WBHBPREIS= :WBHBPREIS,"
                    +"WBHPART= :WBHPART,"
                    +"WBHAVPREIS= :WBHAVPREIS,"
                    +"WBHIPREIS= :WBHIPREIS,"
                    +"WBHINDAT= :WBHINDAT,"
                    +"WBHFILLER= :WBHFILLER,"
                        + " WHERE  "
                        + "  VAKBMANDANT=:VAKBMANDANT AND VAKBKOLL=:VAKBKOLL AND VAKBART =:VAKBART AND VAKBFARBE=:VAKBFARBE AND VAKBKUNDNR=:VAKBKUNDNR";
                    #endregion

                    #region====Parameters====
          or1.Parameters.Add("WBHMANDANT", OracleType.NVarChar).Value = dwData["WBHMANDANT"].ToString(); 
          or1.Parameters.Add("WBHDATE", OracleType.NVarChar).Value = dwData["WBHDATE"].ToString(); 
          or1.Parameters.Add("WBHRWKRS", OracleType.NVarChar).Value = dwData["WBHRWKRS"].ToString(); 
          or1.Parameters.Add("WBHRWLFD", OracleType.NVarChar).Value = dwData["WBHRWLFD"].ToString(); 
          or1.Parameters.Add("WBHPOS", OracleType.NVarChar).Value = dwData["WBHPOS"].ToString(); 
          or1.Parameters.Add("WBHWERK", OracleType.NVarChar).Value = dwData["WBHWERK"].ToString(); 
          or1.Parameters.Add("WBHABT", OracleType.NVarChar).Value = dwData["WBHABT"].ToString(); 
          or1.Parameters.Add("WBHKUNDNR", OracleType.NVarChar).Value = dwData["WBHKUNDNR"].ToString(); 
          or1.Parameters.Add("WBHSTKNR", OracleType.NVarChar).Value = dwData["WBHSTKNR"].ToString(); 
          or1.Parameters.Add("WBHWAKZ", OracleType.NVarChar).Value = dwData["WBHWAKZ"].ToString(); 
          or1.Parameters.Add("WBHART", OracleType.NVarChar).Value = dwData["WBHART"].ToString(); 
          or1.Parameters.Add("WBHDESS", OracleType.NVarChar).Value = dwData["WBHDESS"].ToString(); 
          or1.Parameters.Add("WBHCOLORIT", OracleType.NVarChar).Value = dwData["WBHCOLORIT"].ToString(); 
          or1.Parameters.Add("WBHFARBE", OracleType.NVarChar).Value = dwData["WBHFARBE"].ToString(); 
          or1.Parameters.Add("WBHBESCHICH", OracleType.NVarChar).Value = dwData["WBHBESCHICH"].ToString(); 
          or1.Parameters.Add("WBHZUSTAND", OracleType.NVarChar).Value = dwData["WBHZUSTAND"].ToString(); 
          or1.Parameters.Add("WBHVAR", OracleType.NVarChar).Value = dwData["WBHVAR"].ToString(); 
          or1.Parameters.Add("WBHKOLL", OracleType.NVarChar).Value = dwData["WBHKOLL"].ToString(); 
          or1.Parameters.Add("WBHLO", OracleType.NVarChar).Value = dwData["WBHLO"].ToString(); 
          or1.Parameters.Add("WBHLB", OracleType.NVarChar).Value = dwData["WBHLB"].ToString(); 
          or1.Parameters.Add("WBHLP", OracleType.NVarChar).Value = dwData["WBHLP"].ToString(); 
          or1.Parameters.Add("WBHFERTKZ", OracleType.NVarChar).Value = dwData["WBHFERTKZ"].ToString(); 
          or1.Parameters.Add("WBHZS", OracleType.NVarChar).Value = dwData["WBHZS"].ToString(); 
          or1.Parameters.Add("WBHTAX", OracleType.NVarChar).Value = dwData["WBHTAX"].ToString(); 
          or1.Parameters.Add("WBHISTKLG", OracleType.NVarChar).Value = dwData["WBHISTKLG"].ToString(); 
          or1.Parameters.Add("WBHNETKLG", OracleType.NVarChar).Value = dwData["WBHNETKLG"].ToString(); 
          or1.Parameters.Add("WBHIGEW", OracleType.NVarChar).Value = dwData["WBHIGEW"].ToString(); 
          or1.Parameters.Add("WBHIBRT", OracleType.NVarChar).Value = dwData["WBHIBRT"].ToString(); 
          or1.Parameters.Add("WBHAUFMSL", OracleType.NVarChar).Value = dwData["WBHAUFMSL"].ToString(); 
          or1.Parameters.Add("WBHZUSAM", OracleType.NVarChar).Value = dwData["WBHZUSAM"].ToString(); 
          or1.Parameters.Add("WBHPACKTYP", OracleType.NVarChar).Value = dwData["WBHPACKTYP"].ToString(); 
          or1.Parameters.Add("WBHSPSTA", OracleType.NVarChar).Value = dwData["WBHSPSTA"].ToString(); 
          or1.Parameters.Add("WBHSPGRD", OracleType.NVarChar).Value = dwData["WBHSPGRD"].ToString(); 
          or1.Parameters.Add("WBHSPKZ", OracleType.NVarChar).Value = dwData["WBHSPKZ"].ToString(); 
          or1.Parameters.Add("WBHANZTEILE", OracleType.NVarChar).Value = dwData["WBHANZTEILE"].ToString(); 
          or1.Parameters.Add("WBHRETOURKZ", OracleType.NVarChar).Value = dwData["WBHRETOURKZ"].ToString(); 
          or1.Parameters.Add("WBHHHK1", OracleType.NVarChar).Value = dwData["WBHHHK1"].ToString(); 
          or1.Parameters.Add("WBHHHK2", OracleType.NVarChar).Value = dwData["WBHHHK2"].ToString(); 
          or1.Parameters.Add("WBHHHK3", OracleType.NVarChar).Value = dwData["WBHHHK3"].ToString(); 
          or1.Parameters.Add("WBHHHK4", OracleType.NVarChar).Value = dwData["WBHHHK4"].ToString(); 
          or1.Parameters.Add("WBHHHK5", OracleType.NVarChar).Value = dwData["WBHHHK5"].ToString(); 
          or1.Parameters.Add("WBHHHK6", OracleType.NVarChar).Value = dwData["WBHHHK6"].ToString(); 
          or1.Parameters.Add("WBHHHK7", OracleType.NVarChar).Value = dwData["WBHHHK7"].ToString(); 
          or1.Parameters.Add("WBHHHK8", OracleType.NVarChar).Value = dwData["WBHHHK8"].ToString(); 
          or1.Parameters.Add("WBHHHK9", OracleType.NVarChar).Value = dwData["WBHHHK9"].ToString(); 
          or1.Parameters.Add("WBHHHK10", OracleType.NVarChar).Value = dwData["WBHHHK10"].ToString(); 
          or1.Parameters.Add("WBHHHKG", OracleType.NVarChar).Value = dwData["WBHHHKG"].ToString(); 
          or1.Parameters.Add("WBHAHK1", OracleType.NVarChar).Value = dwData["WBHAHK1"].ToString(); 
          or1.Parameters.Add("WBHAHK2", OracleType.NVarChar).Value = dwData["WBHAHK2"].ToString(); 
          or1.Parameters.Add("WBHAHK3", OracleType.NVarChar).Value = dwData["WBHAHK3"].ToString(); 
          or1.Parameters.Add("WBHAHK4", OracleType.NVarChar).Value = dwData["WBHAHK4"].ToString(); 
          or1.Parameters.Add("WBHAHK5", OracleType.NVarChar).Value = dwData["WBHAHK5"].ToString(); 
          or1.Parameters.Add("WBHAHK6", OracleType.NVarChar).Value = dwData["WBHAHK6"].ToString(); 
          or1.Parameters.Add("WBHAHK7", OracleType.NVarChar).Value = dwData["WBHAHK7"].ToString(); 
          or1.Parameters.Add("WBHAHK8", OracleType.NVarChar).Value = dwData["WBHAHK8"].ToString(); 
          or1.Parameters.Add("WBHAHK9", OracleType.NVarChar).Value = dwData["WBHAHK9"].ToString(); 
          or1.Parameters.Add("WBHAHK10", OracleType.NVarChar).Value = dwData["WBHAHK10"].ToString(); 
          or1.Parameters.Add("WBHAHKG", OracleType.NVarChar).Value = dwData["WBHAHKG"].ToString(); 
          or1.Parameters.Add("WBHABPZAB", OracleType.NVarChar).Value = dwData["WBHABPZAB"].ToString(); 
          or1.Parameters.Add("WBHBPREIS", OracleType.NVarChar).Value = dwData["WBHBPREIS"].ToString(); 
          or1.Parameters.Add("WBHPART", OracleType.NVarChar).Value = dwData["WBHPART"].ToString(); 
          or1.Parameters.Add("WBHAVPREIS", OracleType.NVarChar).Value = dwData["WBHAVPREIS"].ToString(); 
          or1.Parameters.Add("WBHIPREIS", OracleType.NVarChar).Value = dwData["WBHIPREIS"].ToString(); 
          or1.Parameters.Add("WBHINDAT", OracleType.NVarChar).Value = dwData["WBHINDAT"].ToString(); 
          or1.Parameters.Add("WBHFILLER", OracleType.NVarChar).Value = dwData["WBHFILLER"].ToString(); 
                    #endregion
                    break; 
                default:
                    break;
            }


            Connect2Proxy(or1);
        }
    }
}
